#region Copyright (c) 2006-2009 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2009 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Xml;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.Common.Util;
using System.Collections.Generic;
using Widgetsphere.Generator.Forms;
using System.Windows.Forms;

namespace Widgetsphere.Generator
{
  internal class SqlSchemaToModel
  {
    private string mConnectString;
    private string mDbName;

    public SqlSchemaToModel(string connectString)
    {
      this.mConnectString = connectString;
      this.mDbName = new System.Data.SqlClient.SqlConnection(this.mConnectString).Database;
    }

    public void GetProjectFromSqlSchema(WidgetsphereGeneratorProject project)
    {
      ModelRoot root = (ModelRoot)project.RootController.Object;
      string companyName = GetCompanyName();
      string projectName = GetProjectName();
      string databaseName = GetDatabaseName();
      if((companyName != null) && (companyName != ""))
        root.CompanyName = companyName;
      if((projectName != null) && (projectName != ""))
        root.ProjectName = projectName;
      if (databaseName != "")
        root.Database.DatabaseName = databaseName;
      root.Version = "0.0.0.0";

      root.Database.Columns.Clear();
      root.Database.Relations.Clear();
      root.Database.Tables.Clear();

      #region Tables

      SqlDataReader tableReader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, GetSqlDatabaseTables());
      while (tableReader.Read())
      {
        Table currentTable = root.Database.Tables.Add(tableReader["name"].ToString());
				if (!ValidationHelper.ValidDatabaseIdenitifer(currentTable.Name))
				{
					currentTable.CodeFacade = ValidationHelper.MakeDatabaseIdenitifer(currentTable.Name);
				}

        SqlDataReader tableAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, "SELECT name, value FROM  ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + currentTable.Name + "', default, default)");

        //Table Attributes
        while (tableAttributeReader.Read())
        {
          string attribName = string.Empty;
          attribName = tableAttributeReader["name"].ToString();
          if (attribName.ToLower().Equals("ms_description"))
          {
            currentTable.Description = tableAttributeReader["value"].ToString();
          }
          else if (attribName.ToLower().Equals("generated"))
          {
            currentTable.Generated = bool.Parse(tableAttributeReader["value"].ToString());
          }
          else if (attribName.ToLower().Equals("associative"))
          {
            currentTable.AssociativeTable = bool.Parse(tableAttributeReader["value"].ToString());
          }
          else if (attribName.ToLower().Equals("hasHistory"))
          {
            currentTable.HasHistory = bool.Parse(tableAttributeReader["value"].ToString());
          }
          System.Windows.Forms.Application.DoEvents();
        }
				tableAttributeReader.Close();

        //Columns
        IDbConnection connection = DatabaseHelper.GetConnection(mConnectString);
        SqlDataReader columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, GetSqlColumnsForTable(tableReader["name"].ToString()));
        while (columnReader.Read())
        {
          string columnName = columnReader["name"].ToString();
					if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedByColumnName) || StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedDateColumnName))
          {
            currentTable.AllowCreateAudit = true;
          }
					else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedByColumnName) || StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedDateColumnName) ||
            StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date"))
          {
            currentTable.AllowModifiedAudit = true;
          }
					else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.TimestampColumnName))
          {
            currentTable.AllowTimestamp = true;
          }
          else
          {
            Column currentColumn = root.Database.Columns.Add(columnName);

						if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name))
						{
							currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdenitifer(currentColumn.Name);
						}

            currentColumn.ParentTableRef = currentTable.CreateRef();
            currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString());
            if (bool.Parse(columnReader["isIdentity"].ToString()))
              currentColumn.Identity = IdentityTypeConstants.Database;
            else
              currentColumn.Identity = IdentityTypeConstants.None;
						string defaultvalue = columnReader["defaultValue"].ToString().Trim(new char[] { '(', '\'', ')' });
						defaultvalue = defaultvalue.Replace("\n", "");
						defaultvalue = defaultvalue.Replace("\r", "");

						//This is some sort of default pointer, we do not handle this.
						if (defaultvalue.StartsWith("create default ["))
							defaultvalue = "";

						//Just in case some put 'null' in to the default field
						if (currentColumn.AllowNull && defaultvalue.ToLower() == "null")
							defaultvalue = "";

						currentColumn.Default = defaultvalue;
            
						currentColumn.Length = (int)columnReader["length"];
						if (columnReader["isPrimaryKey"] != System.DBNull.Value)
							currentColumn.PrimaryKey = true;

            //currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString());
            try
            {
              string columnTypename = columnReader["datatype"].ToString();
              if(StringHelper.Match(columnTypename, "numeric", true))
                currentColumn.Type = SqlDbType.Decimal;
              else
                currentColumn.Type = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true);
            }
            catch { }
            SqlDataReader columnAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connection, CommandType.Text, "SELECT name, value FROM  ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + currentTable + "', 'column', '" + currentColumn + "')");
            while (columnAttributeReader.Read())
            {
              string attribName = string.Empty;
              attribName = columnAttributeReader["name"].ToString();
              if (attribName.ToLower().Equals("ms_description"))
              {
                currentColumn.Description = columnAttributeReader["value"].ToString();
              }

            }
            columnAttributeReader.Close();
            currentTable.Columns.Add(currentColumn.CreateRef());
          } //Create New Column
        }

				////Get all column descriptions
				//columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, GetSqlColumnsForTable(tableReader["name"].ToString()));
				//while (columnReader.Read())
				//{
				//  Reference reference = currentTable.Columns[columnReader["ColumnName"].ToString()];
				//  if (reference != null)
				//  {
				//    Column column = (Column)reference.Object;
				//    if (column != null)
				//    {
				//      column.Description = columnReader["Description"].ToString();
				//    }
				//  }
				//}

				connection.Close();
        System.Windows.Forms.Application.DoEvents();

				if (currentTable.Name == "sysdiagrams")
				{
					root.Database.Tables.Remove(currentTable);
				}

      }

      #endregion

			#region Relations

			this.LoadRelations(project);

			#endregion

      //Refresh the tree
      project.RootController.Node.RefreshDeep();

    }

		private void LoadRelations(WidgetsphereGeneratorProject project)
		{
			ModelRoot root = (ModelRoot)project.RootController.Object;
			DataSet dsRelationship = DatabaseHelper.ExecuteDataset(mConnectString, GetSqlForRelationships(""));
			foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
			{
				string constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
				string parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
				string childTableName = (string)rowRelationship["FK_TABLE_NAME"];
				Table parentTable = root.Database.Tables[parentTableName];
				Table childTable = root.Database.Tables[childTableName];

				if ((parentTable != null) && (childTable != null))
				{
					Relation relation = null;
					if (!root.Database.Relations.Contains(constraintName))
					{
						relation = root.Database.Relations.Add();
						relation.ParentTableRef = parentTable.CreateRef();
						relation.ChildTableRef = childTable.CreateRef();
						relation.ConstraintName = constraintName;
						parentTable.Relationships.Add(relation.CreateRef());
					}
					else
					{
						relation = root.Database.Relations.GetByName(constraintName);
					}

					//add the column relationship to the relation
					ColumnRelationship columnRelationship = new ColumnRelationship(relation.Root);
					columnRelationship.ParentColumnRef = ((Column)parentTable.Columns[(string)rowRelationship["UQ_COLUMN_NAME"]].Object).CreateRef();
					columnRelationship.ChildColumnRef = ((Column)childTable.Columns[(string)rowRelationship["FK_COLUMN_NAME"]].Object).CreateRef();
					relation.ColumnRelationships.Add(columnRelationship);


					////Get column information
					//SqlDataReader linkReader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, GetSqlForForeignKeys(parentTableName, childTableName, relation.ConstraintName));
					//while (linkReader.Read())
					//{
					//  ColumnRelationship columnRelationship = new ColumnRelationship(relation.Root);
					//  columnRelationship.ParentColumnRef = ((Column)parentTable.Columns[(string)linkReader["parentColumn"]].Object).CreateRef();
					//  columnRelationship.ChildColumnRef = ((Column)childTable.Columns[(string)linkReader["childColumn"]].Object).CreateRef();
					//  relation.ColumnRelationships.Add(columnRelationship);
					//}
					//parentTable.Relationships.Add(relation.CreateRef());

				} //Not Contains constraint

			}
		}

    private string GetProjectName()
    {
      string retval = null;
      SqlDataReader reader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'projectName',   NULL, NULL,NULL, NULL,NULL,NULL)");
      if (reader.Read())
      {
        retval = reader["value"].ToString();
        reader.Close();
      }
      return retval;
    }

    private string GetCompanyName()
    {
      string retval = null;
      try
      {
        SqlDataReader reader = (SqlDataReader)DatabaseHelper.ExecuteReader(mConnectString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'companyName',NULL, NULL,NULL, NULL,NULL,NULL)");
        if (reader.Read())
        {
          retval = reader["value"].ToString();
          reader.Close();
        }
      }
      catch (Exception ex)
      {
        System.Diagnostics.Debug.WriteLine(ex.ToString());
        throw ex;
      }
      return retval;
    }

    private string GetDatabaseName()
    {
      return mDbName;
    }

    private string GetSqlDatabaseProperties()
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT	sysproperties.name,	sysproperties.value ");
      sb.Append("FROM	sysproperties ");
      sb.Append("WHERE	sysproperties.id=0");
      return sb.ToString();
    }

    private string GetSqlDatabaseTables()
    {
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("DECLARE @bar varchar(150)");
			sb.AppendLine("DECLARE @val varchar(150)");
			sb.AppendLine("DECLARE @tab table");
			sb.AppendLine("(");
			sb.AppendLine("xName varchar(150) NOT NULL,");
			sb.AppendLine("xValue varchar(150) NULL");
			sb.AppendLine(")");
			sb.AppendLine("INSERT INTO @tab SELECT 	so.name, null FROM 	sysobjects so WHERE so.xtype = 'U' AND so.name <> 'dtproperties'");
			sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab ORDER BY xName ASC)");
			sb.AppendLine("WHILE @bar is not null");
			sb.AppendLine("BEGIN");
			sb.AppendLine("SET @val = (SELECT CONVERT(varchar(150), value) FROM ::fn_listextendedproperty ( N'selectionCriteria', N'user', N'dbo', N'table',@bar,NULL,NULL))");
			sb.AppendLine("UPDATE @tab	SET xValue = @val where xName = @bar");
			sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab where xName > @bar ORDER BY xName ASC)");
			sb.AppendLine("END");
			sb.AppendLine("select xName as name, xValue selectionCriteria from @tab ORDER BY xName");
			return sb.ToString();
    }

    private string GetSqlPropertiesForTable(string tableName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT sysproperties.name, sysproperties.value ");
      sb.Append("FROM sysproperties ");
      sb.Append("INNER JOIN sysobjects on sysproperties.id = sysobjects.id ");
      sb.Append("WHERE sysobjects.name='").Append(tableName).Append("' AND ");
      sb.Append("sysproperties.smallid=0");
      return sb.ToString();
    }

		private string GetSqlColumsDescription(string tableName)
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,");
			sb.AppendLine("       ep.value AS Description");
			sb.AppendLine("FROM sys.objects");
			sb.AppendLine("INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id");
			sb.AppendLine("CROSS APPLY fn_listextendedproperty(default,");
			sb.AppendLine("                  'SCHEMA', schema_name(schema_id),");
			sb.AppendLine("                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep");
			sb.AppendLine("WHERE sys.objects.name = '" + tableName + "'");
			return sb.ToString();
		}

    private string GetSqlColumnsForTable(string tableName)
    {
      StringBuilder sb = new StringBuilder();
      sb.AppendLine(" SELECT ");
      sb.AppendLine(" 	c.ORDINAL_POSITION as colorder,");
      sb.AppendLine(" 	c.COLUMN_NAME as name,");

			//sb.AppendLine(" 	case WHEN");
			//sb.AppendLine(" 	(");
			//sb.AppendLine(" 		SELECT ");
			//sb.AppendLine(" 				count(*) ");
			//sb.AppendLine(" 			FROM ");
			//sb.AppendLine(" 				INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE primarykeyccu");
			//sb.AppendLine(" 				INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS primarykeytc on primarykeyccu.CONSTRAINT_NAME = primarykeytc.CONSTRAINT_NAME AND");
			//sb.AppendLine(" 																												primarykeyccu.CONSTRAINT_SCHEMA = primarykeytc.CONSTRAINT_SCHEMA AND");
			//sb.AppendLine(" 																												primarykeytc.CONSTRAINT_TYPE = 'PRIMARY KEY'");
			//sb.AppendLine(" 			WHERE");
			//sb.AppendLine(" 				primarykeyccu.TABLE_SCHEMA = c.TABLE_SCHEMA AND");
			//sb.AppendLine(" 				primarykeyccu.TABLE_NAME = c.TABLE_NAME AND");
			//sb.AppendLine(" 				primarykeyccu.COLUMN_NAME = c.COLUMN_NAME ");
			//sb.AppendLine(" 	) > 0 THEN 'true' ELSE 'false' END as isPrimaryKey,");

			sb.AppendLine("(");
			sb.AppendLine("select top 1 c1.name");
			sb.AppendLine("from sysindexes i");
			sb.AppendLine("join sysobjects o ON i.id = o.id");
			sb.AppendLine("join sysobjects pk ON i.name = pk.name");
			sb.AppendLine("AND pk.parent_obj = i.id");
			sb.AppendLine("AND pk.xtype = 'PK'");
			sb.AppendLine("join sysindexkeys ik on i.id = ik.id");
			sb.AppendLine("and i.indid = ik.indid");
			sb.AppendLine("join syscolumns c1 ON ik.id = c1.id");
			sb.AppendLine("AND ik.colid = c1.colid");
			sb.AppendLine("AND c1.name = c.COLUMN_NAME");
			sb.AppendLine("where o.name = '" + tableName + "'");
			sb.AppendLine(") as [isPrimaryKey],");

      sb.AppendLine(" 	case WHEN");
      sb.AppendLine(" 	(");
      sb.AppendLine(" 		SELECT ");
      sb.AppendLine(" 				count(*) ");
      sb.AppendLine(" 			FROM ");
      sb.AppendLine(" 				INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE foreignkeyccu");
      sb.AppendLine(" 				INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS foreignkeytc on foreignkeyccu.CONSTRAINT_NAME = foreignkeytc.CONSTRAINT_NAME AND");
      sb.AppendLine(" 																												foreignkeyccu.CONSTRAINT_SCHEMA = foreignkeytc.CONSTRAINT_SCHEMA AND");
      sb.AppendLine(" 																												foreignkeytc.CONSTRAINT_TYPE = 'FOREIGN KEY'");
      sb.AppendLine(" 			WHERE");
      sb.AppendLine(" 				foreignkeyccu.TABLE_SCHEMA = c.TABLE_SCHEMA AND");
      sb.AppendLine(" 				foreignkeyccu.TABLE_NAME = c.TABLE_NAME AND");
      sb.AppendLine(" 				foreignkeyccu.COLUMN_NAME = c.COLUMN_NAME ");
      sb.AppendLine(" 	) > 0 THEN 'true' ELSE 'false' END as isForeignKey,");
      sb.AppendLine(" 	c.DATA_TYPE as datatype,");
      sb.AppendLine(" 		case when	c.CHARACTER_MAXIMUM_LENGTH is null or c.CHARACTER_MAXIMUM_LENGTH > 8000 then s.length else c.CHARACTER_MAXIMUM_LENGTH end as length,");
      sb.AppendLine(" 	case when c.IS_NULLABLE = 'No' then 'false' else 'true' end as allowNull,  ");
      sb.AppendLine("	case when c.COLUMN_DEFAULT is null then '' else REPLACE(REPLACE(REPLACE(REPLACE(c.COLUMN_DEFAULT,'(N''',''),')',''),'(',''),'''','') end as defaultValue,");
			sb.AppendLine(" 	case when COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'true' else 'false' end as isIdentity");
      sb.AppendLine(" FROM ");
      sb.AppendLine(" 	INFORMATION_SCHEMA.COLUMNS c ");
      sb.AppendLine(" 	INNER JOIN systypes s on s.name = c.DATA_TYPE");
      sb.AppendLine(" WHERE");
			sb.AppendLine(" 	c.TABLE_NAME  = '" + tableName + "'");
      sb.AppendLine(" ORDER BY");
      sb.AppendLine(" 	c.TABLE_NAME,");
      sb.AppendLine(" 	c.COLUMN_NAME");
      return sb.ToString();
    }
		
    private string GetSqlForColumnProperties(string tableName, string columnName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT sysproperties.name,	sysproperties.value ");
      sb.Append("FROM sysproperties ");
      sb.Append("inner join sysobjects on sysproperties.id = sysobjects.id ");
      sb.Append("inner join syscolumns on syscolumns.id = sysobjects.id AND ");
      sb.Append("syscolumns.colorder = sysproperties.smallid ");
      sb.Append("WHERE ");
      sb.Append("sysobjects.name = '").Append(tableName).Append("' AND ");
      sb.Append("syscolumns.name = '").Append(columnName).Append("'");
      return sb.ToString();
    }

    private string GetSqlForRelationships(string tableName)
    {
			//StringBuilder sb = new StringBuilder();
			//sb.Append("DECLARE @table_name varchar(256) ");
			//sb.Append("set @table_name = '").Append(tableName).Append("' ");
			//sb.Append("SELECT DISTINCT  ");
			//sb.Append("	parent.name as Parent,  ");
			//sb.Append("	child.name as Child,  ");
			//sb.Append("	case when parent.name = @table_name then 'parent' else 'child' end as rolePlayed,  ");
			//sb.Append("	relation.name as constraintName,  ");
			//sb.Append("	case when roleNameProvider.value is null then '' else roleNameProvider.value end as roleName  ");
			//sb.Append("FROM  ");
			//sb.Append("	sysforeignkeys  ");
			//sb.Append("		inner join sysobjects relation on constid = relation.id ");
			//sb.Append("		inner join sysobjects child on fkeyid = child.id  ");
			//sb.Append("		inner join sysobjects parent on rkeyid = parent.id   ");
			//sb.Append("		left outer join sysproperties roleNameProvider on roleNameProvider.id = relation.id  ");
			//sb.Append("WHERE  ");
			//sb.Append("	parent.name = @table_name  ");
			//sb.Append("OR  ");
			//sb.Append("	child.name=@table_name ");
			//sb.Append("union ");
			//sb.Append("SELECT  UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 )) as Parent, ");
			//sb.Append("c.TABLE_NAME as Child, ");
			//sb.Append("'child', ");
			//sb.Append("'PSEUDO_' + c.TABLE_NAME + '_' + UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 ) + '_' + c.COLUMN_NAME), ");
			//sb.Append("c.COLUMN_NAME ");
			//sb.Append("FROM ");
			//sb.Append("	INFORMATION_SCHEMA.COLUMNS c ");
			//sb.Append("WHERE  ");
			//sb.Append("	c.TABLE_NAME = @table_name ");
			//sb.Append("and  ");
			//sb.Append("	c.COLUMN_NAME like 'fk_to_%' ");
			//sb.Append("union ");
			//sb.Append("SELECT DISTINCT ");
			//sb.Append("	@table_name as Parent, ");
			//sb.Append("c.TABLE_NAME as Child,  ");
			//sb.Append("'parent' as rolePlayed, ");
			//sb.Append("'PSEUDO_' + c.TABLE_NAME + '_' + UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 ) + '_' + c.COLUMN_NAME) as constraintName, ");
			//sb.Append("c.COLUMN_NAME as roleName ");
			//sb.Append("FROM  ");
			//sb.Append("	INFORMATION_SCHEMA.COLUMNS c ");
			//sb.Append("WHERE ");
			//sb.Append("c.COLUMN_NAME like 'fk_to_%' ");
			//sb.Append("AND ");
			//sb.Append("@table_name = SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 )");
			//return sb.ToString();

			//StringBuilder sb = new StringBuilder();
			//sb.Append("SELECT ");
			//sb.Append("FK.TABLE_NAME AS [Child], ");
			//sb.Append("CU.COLUMN_NAME AS [ChildColumn], ");
			//sb.Append("PK.TABLE_NAME AS [Parent], ");
			//sb.Append("PT.COLUMN_NAME AS [ParentColumn], ");
			//sb.Append("C.CONSTRAINT_NAME AS [constraintName] ");
			//sb.Append("FROM    	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ");
			//sb.Append("INNER JOIN 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN    	INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN    	INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN	( ");
			//sb.Append("SELECT 		i1.TABLE_NAME, i2.COLUMN_NAME ");
			//sb.Append("FROM   		INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 ");
			//sb.Append("INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME ");
			//sb.Append("WHERE 		i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ");
			//sb.Append(") PT ON PT.TABLE_NAME = PK.TABLE_NAME ");

			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT ");
			sb.AppendLine("		 KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'");
			sb.AppendLine("	, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'");
			sb.AppendLine("	, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'   ");
			sb.AppendLine("	, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'");
			sb.AppendLine("	, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'   ");
			sb.AppendLine("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC");
			sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1");
			sb.AppendLine("ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG ");
			sb.AppendLine("	AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA");
			sb.AppendLine("	AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
			sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2");
			sb.AppendLine("ON KCU2.CONSTRAINT_CATALOG = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_CATALOG ");
			sb.AppendLine("	AND KCU2.CONSTRAINT_SCHEMA = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_SCHEMA");
			sb.AppendLine("	AND KCU2.CONSTRAINT_NAME = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_NAME");
			sb.AppendLine("	AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("	KCU1.CONSTRAINT_NAME,");
			sb.AppendLine("	KCU1.ORDINAL_POSITION");

			return sb.ToString();
		
		}

    private string GetSqlForForeignKeys(string parentTable, string childTable, string constraintName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("DECLARE @parent_table varchar(256) ");
      sb.Append("DECLARE @child_table varchar(256) ");
      sb.Append("DECLARE @constraint varchar(256) ");
      sb.Append("SET @parent_table = '").Append(parentTable).Append("' ");
      sb.Append("SET @child_table = '").Append(childTable).Append("' ");
      sb.Append("SET @constraint = '").Append(constraintName).Append("' ");
      sb.Append("DECLARE @FKeys TABLE  (  parentTable varchar(100) NOT NULL,   childTable varchar(100) NOT NULL,   childColumn varchar(100) NOT NULL,   constid int NOT NULL,   keyno smallint NOT NULL  )   ");
      sb.Append("DECLARE @PKeys TABLE  (  parentTable varchar(100) NOT NULL,  childTable varchar(100) NOT NULL,  parentColumn varchar(100) NOT NULL,  constid int NOT NULL,   keyno smallint NOT NULL  )   ");
      sb.Append("INSERT INTO @FKeys   SELECT DISTINCT   	parent.name parentTable,   	child.name childTable,    ");
      sb.Append("	syscolumns.name as childColumn,    	sysforeignkeys.constid,   	sysforeignkeys.keyno   FROM    	sysforeignkeys     ");
      sb.Append("		inner join sysobjects child on fkeyid = child.id     ");
      sb.Append("		inner join sysobjects parent on rkeyid = parent.id     ");
      sb.Append("		inner join syscolumns on syscolumns.id = sysforeignkeys.fkeyid AND syscolumns.colorder = sysforeignkeys.fkey     ");
      sb.Append("INSERT INTO @PKeys    ");
      sb.Append("SELECT    	parent.name parentTable,   	child.name childTable,   	syscolumns.name as parentColumn,  	sysforeignkeys.constid,   	sysforeignkeys.keyno   ");
      sb.Append("FROM     ");
      sb.Append("	sysforeignkeys  ");
      sb.Append("	inner join sysobjects child on fkeyid = child.id     ");
      sb.Append("	inner join sysobjects parent on rkeyid = parent.id     ");
      sb.Append("	inner join syscolumns on syscolumns.id = sysforeignkeys.rkeyid AND syscolumns.colorder = sysforeignkeys.rkey   ");
      sb.Append("SELECT  ");
      sb.Append("	p.parentTable , ");
      sb.Append("	p.parentColumn,  ");
      sb.Append("	f.childTable,  ");
      sb.Append("	f.ChildColumn ,  ");
      sb.Append("	so.name as roleName ");
      sb.Append("	FROM @FKeys f  ");
      sb.Append("		INNER JOIN @PKeys p on f.constid=p.constID and f.keyno=p.keyno  ");
      sb.Append("		INNER JOIN sysobjects so on so.id = p.constid   ");
      sb.Append("WHERE  ");
      sb.Append("	f.parentTable = @parent_table  ");
      sb.Append("AND  ");
      sb.Append("	f.childTable = @child_table  ");
      sb.Append("AND  ");
      sb.Append("	so.name = @constraint  ");
      sb.Append("union ");
      sb.Append("SELECT  ");
      sb.Append("@parent_table as parentTable, ");
      sb.Append("lower (@parent_table) + '_id' as parentColumn, ");
      sb.Append("@child_table as child_table, ");
      sb.Append("c.COLUMN_NAME as child_column, ");
      sb.Append("@constraint as roleName ");
      sb.Append("FROM INFORMATION_SCHEMA.COLUMNS c WHERE ");
      sb.Append("   c.TABLE_NAME = @child_table  ");
      sb.Append("AND ");
      sb.Append("	c.COLUMN_NAME like 'fk_to_' + @parent_table + '%' ");
      sb.Append("AND ");
      sb.Append("@parent_table like '%_HIST' ");
      sb.Append("	AND  ");
      sb.Append("SUBSTRING(@constraint,	CHARINDEX ('__',@constraint)+ 2 ,LEN(@constraint)- CHARINDEX ('__',@constraint)) ");
      sb.Append(" =  ");
      sb.Append("SUBSTRING(c.COLUMN_NAME,	CHARINDEX ('__',c.COLUMN_NAME)+ 2 ,LEN(c.COLUMN_NAME)- CHARINDEX ('__',c.COLUMN_NAME)) ");

      return sb.ToString();
    }

		public enum ImportReturnConstants
		{
			Aborted,
			Success,
			NoChange,
		}

		public ImportReturnConstants ImportModel(WidgetsphereGeneratorProject currentGraph)
		{
			try
			{
				WidgetsphereGeneratorProject newProject = new WidgetsphereGeneratorProject();
				GetProjectFromSqlSchema(newProject);

				Widgetsphere.Generator.Models.Database newDatabase = ((ModelRoot)newProject.RootController.Object).Database;
				Widgetsphere.Generator.Models.Database oldDatabase = ((ModelRoot)currentGraph.RootController.Object).Database;

				ImportVerificationForm F = new ImportVerificationForm();
				ImportModelSettings settings = F.Populate(oldDatabase, newDatabase);

				//Check if there are any changes and if so show the form
				if (F.GetChangeCount() == 0)
				{					
					return ImportReturnConstants.NoChange;
				}
				else if (F.ShowDialog() != System.Windows.Forms.DialogResult.OK)
				{
					return  ImportReturnConstants.Aborted;
				}

				#region Update existing tables
				foreach (Table t in oldDatabase.Tables)
				{
					Table newT = newDatabase.Tables[t.Name];
					if (newT != null)
					{
						#region Update matching columns
						List<Reference> delRefList = new List<Reference>();
						foreach (Reference r in t.Columns)
						{
							Column oldCol = (Column)r.Object;
							Reference newColRef = newT.Columns.FindByName(oldCol.Name);
							Column newCol = null;
							if (newColRef != null) newCol = (Column)newColRef.Object;

							bool doUpdate = ((newCol != null) && (oldCol.CorePropertiesHash != newCol.CorePropertiesHash));
							if (doUpdate && settings.OverridePrimaryKey)
								doUpdate = (oldCol.CorePropertiesHashNoPK != newCol.CorePropertiesHashNoPK);

							if (doUpdate)
							{
								oldCol.CancelUIEvents = true;
								int colId = oldCol.Id;
								string colKey = oldCol.Key;
								bool isPrimaryKey = oldCol.PrimaryKey;
								newCol.CodeFacade = oldCol.CodeFacade;
								//newCol.SetId(colId);
								//newCol.SetKey(colKey);
								XmlDocument doc = new XmlDocument();
								doc.LoadXml("<a></a>");
								newCol.XmlAppend(doc.DocumentElement);
								oldCol.XmlLoad(doc.DocumentElement);
								oldCol.SetId(colId);
								oldCol.SetKey(colKey);
								//oldCol.PrimaryKey = isPrimaryKey;
								oldCol.CancelUIEvents = false;
							}
							else if (newCol == null)
							{
								delRefList.Add(r);
							}

						}

						//Remove deleted fields
						foreach (Reference r in delRefList)
						{
							t.Columns.Remove(r);
						}
						#endregion

						#region Add new columns
						foreach (Reference r in newT.Columns)
						{
							Column c = (Column)r.Object;
							if (!t.GetColumns().Contains(c.Name))
							{
								Column newCol = oldDatabase.Columns.Add(c.Name);
								int colId = newCol.Id;
								XmlDocument doc = new XmlDocument();
								doc.LoadXml("<a></a>");
								c.XmlAppend(doc.DocumentElement);
								newCol.XmlLoad(doc.DocumentElement);
								newCol.SetId(colId);
								newCol.ParentTableRef = t.CreateRef();

								Reference newRef = new Reference(t.Root);
								newRef.Ref = newCol.Id;
								newRef.RefType = ReferenceType.Column;
								t.Columns.Add(newRef);
							}
						}
						#endregion

						#region Delete old columns
						List<Column> delColumnList = new List<Column>();
						foreach (Reference r in t.Columns)
						{
							if (!newT.GetColumns().Contains(((Column)r.Object).Name))
							{
								delColumnList.Add((Column)r.Object);
							}
						}
						foreach (Column c in delColumnList)
						{
							oldDatabase.Columns.Remove(c);
						}
						#endregion

						#region Update table properties
						t.CancelUIEvents = true;						
						t.Description = newT.Description;
						t.AssociativeTable = newT.AssociativeTable;
						t.Generated = newT.Generated;
						t.HasHistory = newT.HasHistory;
						t.AllowModifiedAudit = newT.AllowModifiedAudit;
						t.AllowCreateAudit = newT.AllowCreateAudit;
						t.AllowTimestamp = newT.AllowTimestamp;
						t.IsTypeTable = newT.IsTypeTable;
						t.CreateMetaData = newT.CreateMetaData;
						t.FullIndexSearch = newT.FullIndexSearch;
						t.IsMetaData = newT.IsMetaData;
						t.IsMetaDataDefinition = newT.IsMetaDataDefinition;
						t.IsMetaDataMaster = newT.IsMetaDataMaster;
						t.CancelUIEvents = false;
						#endregion
					}

				}
				#endregion

				#region Add new tables
				foreach (Table t in newDatabase.Tables)
				{
					if (!oldDatabase.Tables.Contains(t.Name))
					{
						Table newT = oldDatabase.Tables.Add(t.Name);
						XmlDocument doc = new XmlDocument();
						int tableId = newT.Id;
						doc.LoadXml("<a></a>");
						t.XmlAppend(doc.DocumentElement);
						newT.XmlLoad(doc.DocumentElement);
						newT.SetId(tableId);

						newT.Columns.Clear();
						foreach (Reference r in t.Columns)
						{
							Column newCol = oldDatabase.Columns.Add(((Column)r.Object).Name);
							int colId = newCol.Id;
							XmlDocument doc3 = new XmlDocument();
							doc3.LoadXml("<a></a>");
							((Column)r.Object).XmlAppend(doc3.DocumentElement);
							newCol.XmlLoad(doc3.DocumentElement);
							newCol.SetId(colId);
							newCol.ParentTableRef = newT.CreateRef();

							Reference newRef = new Reference(newT.Root);
							newRef.Ref = newCol.Id;
							newRef.RefType = ReferenceType.Column;
							newT.Columns.Add(newRef);
						}
						newT.Relationships.Clear();

					}
				}
				#endregion

				#region Delete removed tables
				List<Table> delTableList = new List<Table>();
				foreach (Table t in oldDatabase.Tables)
				{
					if (!newDatabase.Tables.Contains(t.Name))
					{
						delTableList.Add(t);
					}
				}
				foreach (Table t in delTableList)
				{
					oldDatabase.Tables.Remove(t);
				}
				#endregion

				#region Relations
				List<Relation> oldList = new List<Relation>();
				foreach(Relation r in oldDatabase.Relations)
					oldList.Add(r);

				oldDatabase.Relations.Clear();
				this.LoadRelations(currentGraph);

				//Now reset the role names where necessary
				foreach (Relation r1 in oldDatabase.Relations)
				{
					foreach (Relation r2 in oldList)
					{
						if (r1.Equals(r2) && (r1.RoleName != r2.RoleName)) r1.RoleName = r2.RoleName;
					}
				}

				#endregion

				//Refresh the tree
				currentGraph.RootController.Node.RefreshDeep();

				return  ImportReturnConstants.Success;

			}
			catch (Exception ex)
			{
				throw;
			}

		}

  }

	internal class ImportModelSettings
	{
		public bool OverridePrimaryKey { get; set; }
	}

}